/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package depena.urbarama.data;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import java.sql.*;

/**
 *
 * @author Alberto
 */
public class Database 
{
    private String jdbcConnectionString;
    private final String PROJECT_TITLE = "project_title";
    private final String PROJECT_URL = "project_url";
    private final String PROJECT_DESC = "project_desc";
    private final String UPLOAD_DATE = "upload_date";
    private final String OWNER_NAME = "owner_name";
    private final String OWNER_URL = "owner_url";
    private final String LONGITUDE = "longitude";
    private final String LATITUDE = "latitude";
    
    /**
     * Constructor
     * 
     * @param jdbcConnectionString 
     */
    public Database(String jdbcConnectionString) 
    {
        this.jdbcConnectionString = jdbcConnectionString;
    }
    
    /**
     * add json data to database
     * 
     * @param project
     * @return true if successful, false otherwise
     */
    public boolean insertProject(Project project)
    {
        String sql = "INSERT INTO Projects (project_title, " +
                "project_url, project_desc, upload_date, " +
                "owner_name, owner_url, longitude, latitude) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        
        try
        {
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(sql);
            
            statement.setString(1, project.getProject_title());
            statement.setString(2, project.getProject_url());
            statement.setString(3, project.getProject_desc());
            statement.setString(4, project.getUpload_date());
            statement.setString(5, project.getOwner_name());
            statement.setString(6, project.getOwner_url());
            statement.setFloat(7, project.getLongitude());
            statement.setFloat(8, project.getLatitude());
            statement.execute(); 
            
            conn.close();
            
            return true;
        }
        catch (MySQLIntegrityConstraintViolationException ex) 
        {
            System.out.println("MySQLIntegrityConstraintViolationException: "
                    + ex);            
        } 
        catch (SQLException ex) 
        {
            System.out.println("Bad SQL: " + ex);  
        }
        
        return false;
    }
    
    /**
     * 
     * 
     * @param longitude
     * @param latitude
     * @return 
     */
    public UProjectList getProjects()
    {
        UProjectList list = new UProjectList();
        
        try
        {
            String sql = "SELECT DISTINCT * FROM Projects ORDER BY owner_name"
                    + " ASC";
            
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            
            while (rs.next()) 
            {
                UProject project = new UProject();
                project.setProject_title(rs.getString(PROJECT_TITLE));
                project.setProject_url(rs.getString(PROJECT_URL));
                project.setProject_desc(rs.getString(PROJECT_DESC));
                project.setUpload_date(rs.getString(UPLOAD_DATE));
                project.setOwner_name(rs.getString(OWNER_NAME));
                project.setOwner_url(rs.getString(OWNER_URL));
                project.setLongitude(rs.getFloat(LONGITUDE));
                project.setLatitude(rs.getFloat(LATITUDE));
                list.add(project);
            }
            
            conn.close();
        }
        catch (MySQLIntegrityConstraintViolationException ex) 
        {
            System.out.println("MySQLIntegrityConstraintViolationException: "
                    + ex);            
        } 
        catch (SQLException ex) 
        {
            System.out.println("Bad SQL: " + ex);  
        }
         
        return list;
    }
    
    public UProjectList getProjectsByOwner(String owner)
    {
        UProjectList list = new UProjectList();
        
        try
        {
            String sql = "SELECT DISTINCT * FROM Projects WHERE "
                    + "owner_name LIKE ?";
            
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, owner + "%");
            ResultSet rs = statement.executeQuery();
            
            while (rs.next()) 
            {
                UProject project = new UProject();
                project.setProject_title(rs.getString(PROJECT_TITLE));
                project.setProject_url(rs.getString(PROJECT_URL));
                project.setProject_desc(rs.getString(PROJECT_DESC));
                project.setUpload_date(rs.getString(UPLOAD_DATE));
                project.setOwner_name(rs.getString(OWNER_NAME));
                project.setOwner_url(rs.getString(OWNER_URL));
                project.setLongitude(rs.getFloat(LONGITUDE));
                project.setLatitude(rs.getFloat(LATITUDE));
                list.add(project);
            }
            
            conn.close();
        }
        catch (MySQLIntegrityConstraintViolationException ex) 
        {
            System.out.println("MySQLIntegrityConstraintViolationException: "
                    + ex);            
        } 
        catch (SQLException ex) 
        {
            System.out.println("Bad SQL: " + ex);  
        }
         
        return list;
    }
    
    public UProjectList getProjectsByTitle(String title)
    {
        UProjectList list = new UProjectList();
        
        try
        {
            String sql = "SELECT DISTINCT * FROM Projects WHERE "
                    + "project_title LIKE ?";
            
            Connection conn = DriverManager.getConnection(jdbcConnectionString); 
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, title + "%");
            ResultSet rs = statement.executeQuery();
            
            while (rs.next()) 
            {
                UProject project = new UProject();
                project.setProject_title(rs.getString(PROJECT_TITLE));
                project.setProject_url(rs.getString(PROJECT_URL));
                project.setProject_desc(rs.getString(PROJECT_DESC));
                project.setUpload_date(rs.getString(UPLOAD_DATE));
                project.setOwner_name(rs.getString(OWNER_NAME));
                project.setOwner_url(rs.getString(OWNER_URL));
                project.setLongitude(rs.getFloat(LONGITUDE));
                project.setLatitude(rs.getFloat(LATITUDE));
                list.add(project);
            }
            
            conn.close();
        }
        catch (MySQLIntegrityConstraintViolationException ex) 
        {
            System.out.println("MySQLIntegrityConstraintViolationException: "
                    + ex);            
        } 
        catch (SQLException ex) 
        {
            System.out.println("Bad SQL: " + ex);  
        }
         
        return list;
    }
}
